package com.agriculture.monitoring.repository;

import com.agriculture.monitoring.entity.GeoParcel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ParcelRepository extends JpaRepository<GeoParcel, Long> {
    
    Optional<GeoParcel> findByParcelCode(String parcelCode);
    
    List<GeoParcel> findByCropType(String cropType);
    
    // 使用MySQL空间函数查询包含指定点的地块
    @Query(value = "SELECT p.* FROM geo_parcel p WHERE " +
            "ST_Contains(ST_GeomFromText(p.parcel_geometry), " +
            "ST_GeomFromText(CONCAT('POINT(', :lng, ' ', :lat, ')')))", 
            nativeQuery = true)
    List<GeoParcel> findParcelsContainingPoint(@Param("lng") Double longitude, 
                                              @Param("lat") Double latitude);
    
    // 计算地块面积（使用MySQL空间函数）
    @Query(value = "SELECT id, parcel_name, ST_Area(parcel_geometry) as area " +
            "FROM geo_parcel WHERE id = :parcelId", nativeQuery = true)
    Object[] calculateParcelArea(@Param("parcelId") Long parcelId);
}